﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace HarveyNash.RefactoringAndCodeSmell.CodeSmell.LongMethod
{
    public static class After
    {
        public static void WriteToExcel(DataSet ds, String fileName, bool append, string excelConnectionString)
        {
            if (File.Exists(fileName) && !append)
                File.Delete(fileName);

            using (OleDbConnection excelFile = CreateExcelFile(excelConnectionString))
            {
                foreach (DataTable dt in ds.Tables)
                {
                    CreateSheet(fileName, append, excelFile, dt);
                    WriteToSheet(excelFile, ds, dt);
                }
            }
        }

        private static OleDbConnection CreateExcelFile(string excelConnectionString)
        {
            OleDbConnection excelFile = new OleDbConnection(excelConnectionString);
            excelFile.Open();
            return excelFile;
        }

        private static void WriteToSheet(OleDbConnection excelFile, DataSet ds, DataTable dt)
        {
            using (OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter(string.Format("select * from [{0}]", dt.TableName), excelFile))
            {
                using (OleDbCommandBuilder excelCommandBuilder = new OleDbCommandBuilder(excelDataAdapter))
                {
                    excelCommandBuilder.QuotePrefix = "[";
                    excelCommandBuilder.QuoteSuffix = "]";

                    excelDataAdapter.InsertCommand = excelCommandBuilder.GetInsertCommand();
                    excelDataAdapter.Update(ds, dt.TableName);
                }
            }
        }

        private static void CreateSheet(String fileName, bool append, OleDbConnection excelFile, DataTable dt)
        {
            if (!File.Exists(fileName) || !append)
            {
                using (OleDbCommand excelCommand = new OleDbCommand(GetCreateSheetCommandText(dt), excelFile))
                {
                    excelCommand.ExecuteNonQuery();
                }
            }
        }

        private static string GetCreateSheetCommandText(DataTable dt)
        {
            StringBuilder command = new StringBuilder();

            command.AppendFormat("CREATE TABLE [{0}] (", dt.TableName);
            foreach (DataColumn dc in dt.Columns)
            {
                command.AppendFormat("[{0}] {1}{2}", dc.ColumnName, getColumnType(dc)
                    , (dc.Ordinal == dt.Columns.Count - 1 ? ")" : ","));
            }
            return command.ToString();
        }

        private static string getColumnType(DataColumn dc)
        {
            string columnType = "TEXT";
            switch (dc.DataType.ToString())
            {
                case "System.Int64":
                case "System.Double":
                case "System.Int32":
                    columnType = "NUMERIC";
                    break;
                default:
                    columnType = "TEXT";
                    break;
            }
            return columnType;
        }
    }
}
